﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Finisar.SQLite;
using System.Data;

using GPTAS.Core;

namespace GPTAS.Database
{
    public class Database
    {
        private static readonly Database _instance = new Database();


        private Database()
        {
            ConnectionString = String.Format("Data Source=" + Configuration.Instance.DatabasePath + Configuration.Instance.DatabaseName + ";Version=3;New=False;Compress=True;");
        }

        public static Database Instance
        {
            get { return _instance; }
        }

        private string ConnectionString
        {
            get;
            set;
        }

        private SQLiteConnection Connection
        {
            get;
            set;
        }

        public int NewAllocation(string allocationname)
        {
            int result = 0;
            using (Connection = new SQLiteConnection(ConnectionString))
            {
                Connection.Open();
                using (SQLiteCommand cmd = new SQLiteCommand("insert into Allocation(Name) values('" + allocationname + "')"))
                {
                    cmd.Connection = Connection;
                    result = cmd.ExecuteNonQuery();
                    if (result > 0)
                    {
                        result = GetCurrentAllocationId();
                    }
                }
            }
            return result;
        }

        public int NewGroup(int allocationid, string groupname)
        {
            int result = 0;
            using (Connection = new SQLiteConnection(ConnectionString))
            {
                Connection.Open();
                using (SQLiteCommand cmd = new SQLiteCommand("insert into 'Group' (AllocationId,Name) values(" + allocationid + ",'" + groupname + "')"))
                {
                    cmd.Connection = Connection;
                    result = cmd.ExecuteNonQuery();
                    if (result > 0)
                    {
                        result = GetCurrentGroupId();
                    }
                }
            }
            return result;
        }

        public int NewItem(string itemname)
        {
            int result = 0;
            using (Connection = new SQLiteConnection(ConnectionString))
            {
                Connection.Open();
                using (SQLiteCommand cmd = new SQLiteCommand("insert into item(Name) values('" + itemname + "')"))
                {
                    cmd.Connection = Connection;
                    result = cmd.ExecuteNonQuery();
                }
            }
            return result;
        }

        private int GetCurrentAllocationId()
        {
            int result = 0;
            using (Connection = new SQLiteConnection(ConnectionString))
            {
                Connection.Open();
                using (SQLiteCommand cmd = new SQLiteCommand("Select Id from Allocation order by Id desc limit 1"))
                {
                    cmd.Connection = Connection;
                    using (SQLiteDataReader dr = cmd.ExecuteReader())
                    {
                        dr.Read();
                        result = Convert.ToInt32(dr[0]);
                    }
                }
            }
            return result;
        }

        private int GetCurrentGroupId()
        {
            int result = 0;
            using (Connection = new SQLiteConnection(ConnectionString))
            {
                Connection.Open();
                using (SQLiteCommand cmd = new SQLiteCommand("Select Id from 'Group' order by Id desc limit 1"))
                {
                    cmd.Connection = Connection;
                    using (SQLiteDataReader dr = cmd.ExecuteReader())
                    {
                        dr.Read();
                        result = Convert.ToInt32(dr[0]);
                    }
                }
            }
            return result;
        }

        public DataTable GetGroups(int allocationid)
        {
            DataTable result = new DataTable();
            using (Connection = new SQLiteConnection(ConnectionString))
            {
                Connection.Open();
                using (SQLiteCommand cmd = new SQLiteCommand("Select * from Group where allocationid=" + allocationid))
                {
                    cmd.Connection = Connection;
                    using (SQLiteDataReader dr = cmd.ExecuteReader())
                    {
                        result.Load(dr);
                    }
                }
            }
            return result;
        }

        public DataTable GetItems(int allocationid)
        {
            DataTable result = new DataTable();
            using (Connection = new SQLiteConnection(ConnectionString))
            {
                Connection.Open();
                using (SQLiteCommand cmd = new SQLiteCommand("Select * from Item where allocationid=" + allocationid))
                {
                    cmd.Connection = Connection;
                    using (SQLiteDataReader dr = cmd.ExecuteReader())
                    {
                        result.Load(dr);
                    }
                }
            }
            return result;
        }

        public DataTable GetItemsFromGroups()
        {
            DataTable result = new DataTable();
            using (Connection = new SQLiteConnection(ConnectionString))
            {
                Connection.Open();
                using (SQLiteCommand cmd = new SQLiteCommand("Select * from IteminGroup where allocationid"))
                {
                    cmd.Connection = Connection;
                    using (SQLiteDataReader dr = cmd.ExecuteReader())
                    {
                        result.Load(dr);
                    }
                }
            }
            return result;
        }

        public DataTable GetAllocations()
        {
            DataTable result = new DataTable();
            using (Connection = new SQLiteConnection(ConnectionString))
            {
                Connection.Open();
                using (SQLiteCommand cmd = new SQLiteCommand("Select * from Allocation"))
                {
                    cmd.Connection = Connection;
                    using (SQLiteDataReader dr = cmd.ExecuteReader())
                    {
                        result.Load(dr);
                    }
                }
            }
            return result;
        }
    }
}
